﻿using Dapper;
using Domain.Entity.System_operation;
using Domain.Entity.System_Ordermanagement;
using Domain.IRepository.Sys_statistics;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Volo.Abp.DependencyInjection;

namespace EFCore.Repository.Sys_statistics
{
    [Dependency(ServiceLifetime.Scoped)]
    public class Order_statisticsRepository : IOrder_statisticsRepository
    {
        public Order_statisticsRepository(MyDbContext myDbContext)
        {
            MyDbContext = myDbContext;
        }

        public MyDbContext MyDbContext { get; set; }

        public int Count()
        {
            string sql = $@"select * from [dbo].[Driver_statistics]";
            return MyDbContext.Database.GetDbConnection().Query(sql).Count();
        }

        public int Count2(string Create_time)
        {
            string[] time = Create_time.Split(',');
            string time1 = time[0];
            string time2 = time[1];
            string sql = $@"select * from [dbo].[Driver_statistics] where 1=1 and Create_time>='{time1}' and Create_time<='{time2}'";
            return MyDbContext.Database.GetDbConnection().Query(sql).Count();
        }

        public int Count3()
        {
            string sql = $@"select * from [dbo].[Orders]";
            return MyDbContext.Database.GetDbConnection().Query(sql).Count();
        }

        public List<Driver_statistics> GetDriver_Statistics(int currentPage, int PageSize)
        {
            string sql = $@"select count(*) as Wb_count,[Dv_id] from [dbo].[Wb_meter]
                            where [Wb_status]='6'
                            group by [Dv_id]";
            List<Driver_statistics> list = MyDbContext.Database.GetDbConnection().Query<Driver_statistics>(sql).ToList();
            return list.Skip((currentPage - 1) * PageSize).Take(PageSize).ToList();
        }

        public List<Driver_statistics> GetDriver_Statistics2(string Create_time, int currentPage, int PageSize)
        {
            string sql = $@"select * from [dbo].[Driver_statistics] where 1=1";
            if (Create_time != null && Create_time != "")
            {
                string[] time = Create_time.Split(',');
                string time1 = time[0];
                string time2 = time[1];
                sql += $@" and Create_time>='{time1}' and Create_time<='{time2}'";
            }
            List<Driver_statistics> list = MyDbContext.Database.GetDbConnection().Query<Driver_statistics>(sql).ToList();
            return list.Skip((currentPage - 1) * PageSize).Take(PageSize).ToList();
        }

        public List<Driver_statistics> GetDriver_Statistics3(int currentPage, int PageSize)
        {
            string sql = $@"select * from [dbo].[Driver_statistics] where 1=1";
            List<Driver_statistics> list = MyDbContext.Database.GetDbConnection().Query<Driver_statistics>(sql).ToList();
            return list.Skip((currentPage - 1) * PageSize).Take(PageSize).ToList();
        }

        public List<Driver_statistics> GetException_log(int Dv_id)
        {
            string sql = $@"select count(*) as Exception_wb,a.[Dv_id] as Dv_id from [dbo].[Wb_meter] as a
                            inner join [dbo].[Exception_log] as  b on a.Wb_id=b.Wb_id
                            where [Wb_status]='6' and a.[Dv_id]='{Dv_id}'
                            group by a.[Dv_id]";
            return MyDbContext.Database.GetDbConnection().Query<Driver_statistics>(sql).ToList();
        }

        public int GetEx_count()
        {
            string sql = $@"select * from [dbo].[Exception_log] where [Mea_status]='55'";
            return MyDbContext.Database.GetDbConnection().Query(sql).Count();
        }

        public int GetOrders_count()
        {
            string sql = $@"select * from [dbo].[Orders] where [Ord_state]='9'";
            return MyDbContext.Database.GetDbConnection().Query(sql).Count();
        }

        public int GetOrders_count2()
        {
            string sql = $@"select * from [dbo].[Orders] where [Ord_state]='5'";
            return MyDbContext.Database.GetDbConnection().Query(sql).Count();
        }

        public List<string> GetOrder_time_year()
        {
            string sql = $@"select YEAR([Create_time]) as year from [dbo].[Orders]
                            group by YEAR([Create_time])";
            return MyDbContext.Database.GetDbConnection().Query<string>(sql).ToList();
        }

        public List<Order_time> GetOreder_time_sum(string year)
        {
            string sql = $@"select DATEPART(month, [Create_time]) as month, count(*) as sum from [dbo].[Orders] where YEAR([Create_time]) = '{year}'
                            group by DATEPART(month, [Create_time])";
            return MyDbContext.Database.GetDbConnection().Query<Order_time>(sql).ToList();
        }

        public List<Driver_statistics> GetWb_ment()
        {
            string sql = $@"select count(*) as Time_wb_count from [dbo].[Wb_meter]
                            where [Wb_status]='6' 
                            AND DATEPART(year, [Planget_time]) = DATEPART(year, [Realityget_time])
                            AND DATEPART(month, [Planget_time]) = DATEPART(month, [Realityget_time])
                            AND DATEPART(day, [Planget_time]) = DATEPART(day, [Realityget_time])
                            group by [Dv_id]";
            return MyDbContext.Database.GetDbConnection().Query<Driver_statistics>(sql).ToList();
        }

        public List<Wb_meter> GetWb_Meters(int Dv_id)
        {
            string sql = $@"select * from [dbo].[Wb_meter] where [Dv_id]='{Dv_id}'";
            return MyDbContext.Database.GetDbConnection().Query<Wb_meter>(sql).ToList();
        }

        public List<Order_time> GetWb_Meters(string Year)
        {
            string sql = $@"select DATEPART(month, [Create_time]) as month, count(*) as sum from [dbo].[Wb_meter] where YEAR([Create_time]) = '{Year}'
                            group by DATEPART(month, [Create_time])";
            return MyDbContext.Database.GetDbConnection().Query<Order_time>(sql).ToList();
        }

        public int GetWb_meter_count()
        {
            string sql = $@"select * from [dbo].[Wb_meter] where Wb_status='3'";
            return MyDbContext.Database.GetDbConnection().Query(sql).Count();
        }

        public int GetWb_meter_count2()
        {
            string sql = $@"select * from [dbo].[Wb_meter] where Wb_status='4'";
            return MyDbContext.Database.GetDbConnection().Query(sql).Count();
        }

        public int UpdateOrder(string Ord_id)
        {
            string sql = $@"update [dbo].[Orders] set [Ord_state]='1' where Ord_id='{Ord_id}'";
            return MyDbContext.Database.GetDbConnection().Execute(sql);
        }
    }
}
